package strukturaRozpis;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Set;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import strukturaSviatky.Sviatky;
import strukturaZamestnanciNepritomnost.Zamestnanci;

public class Rozpis {

    //private variables
    private boolean connected = false;
    private Connection con = null;
    private String url = "jdbc:mysql://kempelen.ii.fmph.uniba.sk:3306/";
    private String db = "vratnica";
    private String driver = "com.mysql.jdbc.Driver";
    private String user = "vratnica";
    private String pass = "MonikaVeronika";
    private TreeMap<Integer, ArrayList<Sluzba>> rozpis; //Integer - cislo dna v danom mesiaci, ArrayList - zoznam sluzieb, po nacitani z databazy su v poradi F denna, M denna, F nocna, M nocna
    private int month, year;
    private int pocetDni;
    private Sviatky s = new Sviatky();

    //public metody
    public Rozpis(int mesiac, int rok) throws SQLException, ParseException {
        month = mesiac;
        year = rok;

        //pocet dni v danom mesiaci a roku
        Calendar c = Calendar.getInstance();
        c.set(year, month - 1, 1);
        pocetDni = c.getActualMaximum(Calendar.DAY_OF_MONTH);
        System.out.println("pocetDni je " + pocetDni);

        rozpis = new TreeMap<>();
        for (int i = 1; i <= pocetDni; i++) {
            rozpis.put(i, new ArrayList<Sluzba>());
        }

        if (connect()) {
            loadSluzby(mesiac, rok);
        } else {
            System.out.println("Nepodarilo sa pripojit k databaze.");
        }
    }

    public TreeMap<Integer, ArrayList<Sluzba>> getRozpis() {
        //vrati cely rozpis na aktualny mesiac s ktorym sa pracuje - pouzivat iba na ziskanie dat, nie na upravu dat!
        return rozpis;
    }

    public boolean addSluzba(Sluzba sluzba) throws SQLException {
        //pridanie sluzby do rozpisu - pouzivat len pri generovani rozpisu
        rozpis.get(sluzba.getDen()).add(sluzba);
        return true;
    }

    public boolean setSluzba(int den, int pozicia, Sluzba sluzba) throws SQLException {
        //nastavenie parametrov sluzby pre dany den a poziciu (F denna, M denna, F nocna, M nocna)
        rozpis.get(den).set(pozicia, sluzba);
        return true;
    }

    public boolean setZamestnanci(int den, int pozicia, int IDzam1, int IDzam2) throws SQLException {
        //nastavenie zamestnancov na zvolenu sluzbu podla dna a pozicie (F denna, M denna, F nocna, M nocna)
        rozpis.get(den).get(pozicia).setZam1(IDzam1);
        rozpis.get(den).get(pozicia).setZam2(IDzam2);
        return true;
    }

    public boolean setZamestnanec1(int den, int pozicia, int IDzam1) throws SQLException {
        //nastavenie zamestnanca 1 na zvolenu sluzbu podla dna a pozicie (F denna, M denna, F nocna, M nocna)
        rozpis.get(den).get(pozicia).setZam1(IDzam1);
        return true;
    }

    public boolean setZamestnanec2(int den, int pozicia, int IDzam2) throws SQLException {
        //nastavenie zamestnanca 2 na zvolenu sluzbu podla dna a pozicie (F denna, M denna, F nocna, M nocna)
        rozpis.get(den).get(pozicia).setZam2(IDzam2);
        return true;
    }

    public boolean commitSluzby() throws SQLException, ParseException {
        //UPLOAD rozpisu do databazy
        Statement st = con.createStatement();

        String fromDate = year + "-" + month + "-1 00:00:00";
        String toDate = year + "-" + month + "-" + pocetDni + " 23:59:59";

        String deleteData = "DELETE FROM zmeny_table WHERE datum_cas BETWEEN '" + fromDate + "' AND '" + toDate + "'";
        st.executeUpdate(deleteData);

        String insertData = "INSERT INTO zmeny_table VALUES ";
        for (int i = 1; i <= pocetDni; i++) {
            for (int j = 0; j < rozpis.get(i).size(); j++) {
                Sluzba sluzba = rozpis.get(i).get(j);
                insertData += "(null, '" + sluzba.getKde() + "', '"
                        + sluzba.getDatumCasSQL() + "', " + sluzba.getID_zam1() + ", " + sluzba.getID_zam2()
                        + "),";
            }
        }
        insertData = insertData.substring(0, insertData.length() - 1);
        st.executeUpdate(insertData);
        System.out.println("Vlozene sluzby");
        return true;
    }

    public int getMonth() {
        return month;
    }

    public int getYear() {
        return year;
    }

    public int getNumberOfDays() {
        return pocetDni;
    }

    public boolean isConnected() {
        return connected;
    }

    public double getPocetHodin(int typZamestnanca) {
        int pocet = 0;
        try {
            Calendar datumCalendar = new GregorianCalendar(year, month, 1);
            for (int i = 1; i <= pocetDni; i++) {
                datumCalendar.set(year, month, i);
                final String DATE_FORMAT = "dd.MM.yyyy";
                SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);

                boolean sviatok = false;
                for (int j = 0; j < s.getSviatky().size(); j++) {
                    if (s.getSviatky().get(j).getDatum().equals(sdf.format(datumCalendar.getTime()))) {
                        sviatok = true;
                        break;
                    }
                }

                if (!(datumCalendar.get(Calendar.DAY_OF_WEEK) == Calendar.SATURDAY) && !(datumCalendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) && !sviatok) {
                    pocet++;
                }
            }

            if (typZamestnanca == 0) {
                return pocet * 7.25;
            }

            if (typZamestnanca == 1) {
                return pocet * 7;
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }


        return 0;
    }

    public TreeMap<Integer, PocetHodinZam> getPocetHodinZamestnancov() { //vrati mapu s ID zamestnancami a strukturov poctu pracovnych hodin za aktualny mesiac
        TreeMap<Integer, PocetHodinZam> result = new TreeMap<>();

        try {

            Sviatky s = new Sviatky();

            Set<Integer> keySet = rozpis.keySet();
            for (Integer key : keySet) {
                ArrayList<Sluzba> sluzby = rozpis.get(key);

                for (int i = 0; i < sluzby.size(); i++) {
                    if (!result.containsKey(
                            sluzby.get(i).getID_zam1())) {
                        result.put(sluzby.get(i).getID_zam1(), new PocetHodinZam());
                    }
                    if (!result.containsKey(
                            sluzby.get(i).getID_zam2())) {
                        result.put(sluzby.get(i).getID_zam2(), new PocetHodinZam());
                    }

                    if (sluzby.get(i).isDenna()) { //denna sluzba
                        result.get(sluzby.get(i).getID_zam1()).cezDen += 12;
                        result.get(sluzby.get(i).getID_zam2()).cezDen += 12;
                        if (sluzby.get(i).isSviatok(s)) { //sviatok
                            result.get(sluzby.get(i).getID_zam1()).cezSviatky += 12;
                            result.get(sluzby.get(i).getID_zam2()).cezSviatky += 12;
                        } else if (sluzby.get(i).isVikend()) { //vikend
                            result.get(sluzby.get(i).getID_zam1()).cezVikendy += 12;
                            result.get(sluzby.get(i).getID_zam2()).cezVikendy += 12;
                        } else { //normal
                            result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 12;
                            result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 12;
                        }
                    } else { //nocna sluzba
                        result.get(sluzby.get(i).getID_zam1()).cezDen += 4;
                        result.get(sluzby.get(i).getID_zam2()).cezDen += 4;
                        result.get(sluzby.get(i).getID_zam1()).cezNoc += 8;
                        result.get(sluzby.get(i).getID_zam2()).cezNoc += 8;

                        if (i < sluzby.size() - 1) { //pri poslednej by to spadlo keby nebola splnena podmienka
                            if (sluzby.get(i).isSviatok(s)) { //sviatok
                                if (sluzby.get(i + 1).isSviatok(s)) { //aj dalsi den je sviatok
                                    result.get(sluzby.get(i).getID_zam1()).cezSviatky += 12;
                                    result.get(sluzby.get(i).getID_zam2()).cezSviatky += 12;
                                } else if (sluzby.get(i + 1).isVikend()) { //dalsi den je vikend
                                    result.get(sluzby.get(i).getID_zam1()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezVikendy += 6;
                                } else {
                                    result.get(sluzby.get(i).getID_zam1()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 6;
                                }
                            } else if (sluzby.get(i).isVikend()) { //vikend
                                if (sluzby.get(i + 1).isVikend()) { //aj dalsi den je vikend
                                    result.get(sluzby.get(i).getID_zam1()).cezVikendy += 12;
                                    result.get(sluzby.get(i).getID_zam2()).cezVikendy += 12;
                                } else if (sluzby.get(i + 1).isSviatok(s)) { //dalsi den je sviatok                                    
                                    result.get(sluzby.get(i).getID_zam1()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezSviatky += 6;
                                } else {
                                    result.get(sluzby.get(i).getID_zam1()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 6;
                                }
                            } else { //normal
                                if (sluzby.get(i + 1).isVikend()) { //dalsi den je normalny den
                                    result.get(sluzby.get(i).getID_zam1()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezVikendy += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 6;
                                } else if (sluzby.get(i + 1).isSviatok(s)) { //dalsi den je sviatok                                    
                                    result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 6;
                                    result.get(sluzby.get(i).getID_zam1()).cezSviatky += 6;
                                    result.get(sluzby.get(i).getID_zam2()).cezSviatky += 6;
                                } else {
                                    result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 12;
                                    result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 12;
                                }
                            }
                        } else {
                            if (sluzby.get(i).isSviatok(s)) { //sviatok
                                result.get(sluzby.get(i).getID_zam1()).cezSviatky += 12;
                                result.get(sluzby.get(i).getID_zam2()).cezSviatky += 12;
                            } else if (sluzby.get(i).isVikend()) { //vikend
                                result.get(sluzby.get(i).getID_zam1()).cezVikendy += 12;
                                result.get(sluzby.get(i).getID_zam2()).cezVikendy += 12;
                            } else { //normal
                                result.get(sluzby.get(i).getID_zam1()).cezNormaleDni += 12;
                                result.get(sluzby.get(i).getID_zam2()).cezNormaleDni += 12;
                            }
                        }
                    }
                }
                result.remove(0);
            }

        } catch (SQLException ex) {
            Logger.getLogger(Rozpis.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ParseException ex) {
            Logger.getLogger(Rozpis.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public ArrayList<Integer> getPoslednuSluzbuMinulehoMesiaca() throws SQLException {
        ArrayList<Integer> result = new ArrayList<>();
        Statement st = con.createStatement();

        Calendar from = new GregorianCalendar(year, month - 1, 1);
        from.add(Calendar.HOUR, -12);
        Calendar to = new GregorianCalendar(year, month - 1, 1);

        String fromDate = from.get(Calendar.YEAR) + "-" + (from.get(Calendar.MONTH) + 1) + "-" + from.get(Calendar.DAY_OF_MONTH) + " 12:00:00";
        String toDate = to.get(Calendar.YEAR) + "-" + (to.get(Calendar.MONTH) + 1) + "-" + to.get(Calendar.DAY_OF_MONTH) + " 00:00:00";

        System.out.println(fromDate + " --- " + toDate);

        ResultSet res = st.executeQuery("SELECT * FROM zmeny_table WHERE datum_cas BETWEEN '" + fromDate + "' AND '" + toDate + "' ORDER BY datum_cas, M_F");

        while (res.next()) {
            if (res.getInt("ID_zam1") != 0) {
                result.add(res.getInt("ID_zam1"));
            }
            if (res.getInt("ID_zam2") != 0) {
                result.add(res.getInt("ID_zam2"));
            }
        }
        return result;
    }

    public ArrayList<Integer> getPrvuSluzbuDalsiehoMesiaca() throws SQLException {
        ArrayList<Integer> result = new ArrayList<>();
        Statement st = con.createStatement();

        Calendar from = new GregorianCalendar(year, month - 1, pocetDni);
        Calendar to = new GregorianCalendar(year, month, 1);

        String fromDate = from.get(Calendar.YEAR) + "-" + (from.get(Calendar.MONTH) + 1) + "-" + from.get(Calendar.DAY_OF_MONTH) + " 23:59:59";
        String toDate = to.get(Calendar.YEAR) + "-" + (to.get(Calendar.MONTH) + 1) + "-" + to.get(Calendar.DAY_OF_MONTH) + " 12:00:00";

        System.out.println(fromDate + " --- " + toDate);

        ResultSet res = st.executeQuery("SELECT * FROM zmeny_table WHERE datum_cas BETWEEN '" + fromDate + "' AND '" + toDate + "' ORDER BY datum_cas, M_F");

        while (res.next()) {
            if (res.getInt("ID_zam1") != 0) {
                result.add(res.getInt("ID_zam1"));
            }
            if (res.getInt("ID_zam2") != 0) {
                result.add(res.getInt("ID_zam2"));
            }
        }
        return result;
    }

    public void vypisRozpis() {
        for (int i = 1; i < pocetDni; i++) {
            for (int j = 0; j < 4; j++) {
                System.out.println(rozpis.get(i).get(j).getDatumCas() + " " + rozpis.get(i).get(j).getKde() + " zam1=" + rozpis.get(i).get(j).getID_zam1() + " zam2=" + rozpis.get(i).get(j).getID_zam2());
            }
        }
    }

    //private metody
    private boolean connect() {
        try {
            Class.forName(driver).newInstance();
            con = DriverManager.getConnection(url + db, user, pass);
            connected = true;
            return true;
        } catch (Exception ex) {
            Logger.getLogger(Sviatky.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    private boolean loadSluzby(int mesiac, int rok) throws SQLException, ParseException {
        Statement st = con.createStatement();

        String fromDate = year + "-" + month + "-1 00:00:00";
        String toDate = year + "-" + month + "-" + pocetDni + " 23:59:59";

        System.out.println(fromDate + " --- " + toDate);

        ResultSet res = st.executeQuery("SELECT * FROM zmeny_table WHERE datum_cas BETWEEN '" + fromDate + "' AND '" + toDate + "' ORDER BY datum_cas, M_F"); //F denna, M denna, F nocna, M nocna

        while (res.next()) {
            Calendar c = new GregorianCalendar();
            final String DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
            SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);

            c.setTime(sdf.parse(res.getString("datum_cas")));

            if ((c.get(Calendar.MONTH) + 1) == mesiac && c.get(Calendar.YEAR) == rok) {
                String kde = res.getString("M_F");
                int zam1 = res.getInt("ID_zam1");
                int zam2 = res.getInt("ID_zam2");
                rozpis.get(c.get(Calendar.DAY_OF_MONTH)).add(new Sluzba(c, kde, zam1, zam2));
            }
        }
        return true;
    }
}
